import javax.swing.*;
import java.awt.Container;
import java.util.Calendar;
import java.util.Vector;
import java.util.List;
import javax.swing.table.*;
import java.util.ArrayList;
import java.text.DecimalFormat;

/* Demand Factor
ratio of the number of checkouts in the past six months and the number of
copies of that movie. For example, if a movie has been checked out fifteen times in the past six
months and there are four copies of the movie, then the demand factor is 15/4 = 3.75.
*/



/*

SELECT TT.title,TT.releasedate,TT.rents,M.copies from (
    select H.title, H.releasedate, Count(title) as rents
        from History H
    WHERE renteddate >= (date ('11/5/2011') - 180 days)
        group by title,releasedate order by Count(title) desc
        fetch first 10 rows only
        ) TT cross join movies M
        where M.releasedate = TT.releasedate AND M.title = TT.title
*/
class DemandFactorDialog extends JDialog{

    Container contentPane;
    String user,
            pass;

    DemandFactorDialog(String user, String pass){
        this.user = user;
        this.pass = pass;
        contentPane = getContentPane();

        setSize(600,400);
        setVisible(true);

        createTable();
    }


    public void createTable(){
        Calendar calendar = Calendar.getInstance();
        int month = calendar.get(Calendar.MONTH);
        int year = calendar.get(Calendar.YEAR);
        int day = calendar.get(Calendar.DAY_OF_MONTH);

        String date = new String(month+ "/" + day + "/" + year);

        String sqlStatement = new String(
        "SELECT TT.title,TT.releasedate,TT.rents,M.copies from ( " +
           " select H.title, H.releasedate, Count(title) as rents " +
           " from History H " +
           " WHERE renteddate >= (date ('"+date+"') - 180 days) " +
               " group by title,releasedate order by Count(title) desc "+
               //" fetch first 10 rows only "+
               " ) TT cross join movies M "+
        " where M.releasedate = TT.releasedate AND M.title = TT.title "

        );

        System.out.println(sqlStatement);
        DBQTable dbq = new DBQTable("comp4410");
        JTable tab;
        try{

            tab = dbq.getTable(user,pass,sqlStatement);
            Vector colNames = new Vector();
            Vector fields = new Vector();
            Vector rows = new Vector();

            colNames.addElement("Title");
            colNames.addElement("Year");
            colNames.addElement("Demand Factor");

            DecimalFormat df = new DecimalFormat("#.##");
            for(int i=0;i<tab.getRowCount();i++){
                fields.addElement(tab.getValueAt(i,0));
                fields.addElement(tab.getValueAt(i,1));

                int rents = Integer.parseInt(tab.getValueAt(i,2).toString());
                int copies = Integer.parseInt(tab.getValueAt(i,3).toString());
                fields.addElement(df.format(rents/(float)copies));

                rows.addElement(fields);
                fields = new Vector();
            }


            for(int j=0;j<10;j++){
                for(int k=rows.size()-1;k>0;k--){
                    Vector temp1 = (Vector)rows.elementAt(k);
                    Vector temp2 = (Vector)rows.elementAt(k-1);
                    if(Float.parseFloat(temp1.elementAt(2).toString()) > Float.parseFloat(temp2.elementAt(2).toString())){
                        rows.remove(k);
                        rows.add(k-1,temp1);
                    }

                }

            }

            Vector topTen = new Vector(10);
            for(int l=0;l<10;l++){
                topTen.add(rows.elementAt(l));

            }
            rows.clear();

            JTable finalTable = new JTable(topTen,colNames);

            DefaultTableModel dfm = (DefaultTableModel)finalTable.getModel();
            dfm.setRowCount(10);

            TableRowSorter<TableModel> sorter
                = new TableRowSorter<TableModel>(finalTable.getModel());
            finalTable.setRowSorter(sorter);

            List <RowSorter.SortKey> sortKeys
                = new ArrayList<RowSorter.SortKey>();
            sortKeys.add(new RowSorter.SortKey(2, SortOrder.DESCENDING));
            sorter.setSortKeys(sortKeys);


            JScrollPane jsp = new JScrollPane(finalTable);
            contentPane.add(jsp);
            contentPane.validate();




        }catch (Exception x){
            x.printStackTrace();
            this.dispose();
        }


    }

}
